const Router = require('koa-router')
const { AISQLLog, User, sequelize } = require('../models')
const auth = require('../middleware/auth')
const { Op } = require('sequelize')

const router = new Router({ prefix: '/api/sql-log' })

// 获取SQL日志列表
router.get('/', auth.authenticateToken, async (ctx) => {
  try {
    const { 
      page = 1, 
      limit = 20, 
      status = '', 
      sql_type = '', 
      risk_level = '',
      user_id = '',
      start_date = '',
      end_date = '',
      search = ''
    } = ctx.query

    const offset = (page - 1) * limit
    const where = {}

    // 状态筛选
    if (status) {
      where.execution_status = status
    }

    // SQL类型筛选
    if (sql_type) {
      where.sql_type = sql_type
    }

    // 风险等级筛选
    if (risk_level) {
      where.risk_level = risk_level
    }

    // 用户筛选
    if (user_id) {
      where.user_id = user_id
    }

    // 时间范围筛选
    if (start_date || end_date) {
      where.created_at = {}
      if (start_date) {
        where.created_at[Op.gte] = new Date(start_date)
      }
      if (end_date) {
        where.created_at[Op.lte] = new Date(end_date)
      }
    }

    // 关键词搜索
    if (search) {
      where[Op.or] = [
        { user_query: { [Op.like]: `%${search}%` } },
        { sql_statement: { [Op.like]: `%${search}%` } },
        { target_table: { [Op.like]: `%${search}%` } },
        { error_message: { [Op.like]: `%${search}%` } }
      ]
    }

    const { count, rows } = await AISQLLog.findAndCountAll({
      where,
      include: [
        {
          model: User,
          as: 'executor',
          attributes: ['id', 'username', 'real_name']
        },
        {
          model: User,
          as: 'approver',
          attributes: ['id', 'username', 'real_name'],
          required: false
        }
      ],
      order: [['created_at', 'DESC']],
      limit: parseInt(limit),
      offset: parseInt(offset)
    })

    ctx.body = {
      success: true,
      data: {
        list: rows,
        pagination: {
          current: parseInt(page),
          pageSize: parseInt(limit),
          total: count,
          pages: Math.ceil(count / limit)
        }
      },
      message: '获取SQL日志成功'
    }
  } catch (error) {
    console.error('获取SQL日志失败:', error)
    ctx.status = 500
    ctx.body = {
      success: false,
      message: '获取SQL日志失败',
      error: error.message
    }
  }
})

// 获取SQL日志详情
router.get('/:id', auth.checkPermission('system:manage'), async (ctx) => {
  try {
    const { id } = ctx.params

    const log = await AISQLLog.findByPk(id, {
      include: [
        {
          model: User,
          as: 'executor',
          attributes: ['id', 'username', 'real_name', 'email']
        },
        {
          model: User,
          as: 'approver',
          attributes: ['id', 'username', 'real_name', 'email'],
          required: false
        }
      ]
    })

    if (!log) {
      ctx.status = 404
      ctx.body = {
        success: false,
        message: 'SQL日志不存在'
      }
      return
    }

    ctx.body = {
      success: true,
      data: log,
      message: '获取SQL日志详情成功'
    }
  } catch (error) {
    console.error('获取SQL日志详情失败:', error)
    ctx.status = 500
    ctx.body = {
      success: false,
      message: '获取SQL日志详情失败',
      error: error.message
    }
  }
})

// 创建SQL日志记录
router.post('/', auth.checkPermission('chat:write'), async (ctx) => {
  try {
    const {
      session_id,
      user_query,
      ai_response,
      sql_statement,
      sql_type,
      target_table,
      risk_level = 'low',
      ai_model = 'deepseek-chat',
      token_usage = 0
    } = ctx.request.body

    const user_id = ctx.state.user.userId

    // 获取客户端信息
    const ip_address = ctx.request.ip || ctx.request.header['x-forwarded-for'] || ctx.request.socket.remoteAddress
    const user_agent = ctx.request.header['user-agent']

    // 自动检测SQL类型
    let detectedSqlType = sql_type
    if (sql_statement && !detectedSqlType) {
      detectedSqlType = detectSQLType(sql_statement)
    }

    // 自动检测风险等级
    let detectedRiskLevel = risk_level
    if (sql_statement) {
      detectedRiskLevel = assessRiskLevel(sql_statement, detectedSqlType)
    }

    const log = await AISQLLog.create({
      user_id,
      session_id,
      user_query,
      ai_response,
      sql_statement,
      sql_type: detectedSqlType,
      target_table,
      execution_status: 'pending',
      risk_level: detectedRiskLevel,
      ip_address,
      user_agent,
      ai_model,
      token_usage
    })

    ctx.body = {
      success: true,
      data: log,
      message: 'SQL日志记录创建成功'
    }
  } catch (error) {
    console.error('创建SQL日志记录失败:', error)
    ctx.status = 500
    ctx.body = {
      success: false,
      message: '创建SQL日志记录失败',
      error: error.message
    }
  }
})

// 更新SQL执行结果
router.put('/:id/result', auth.checkPermission('system:manage'), async (ctx) => {
  try {
    const { id } = ctx.params
    const {
      execution_status,
      execution_time,
      rows_affected,
      error_message,
      execution_result
    } = ctx.request.body

    const log = await AISQLLog.findByPk(id)
    if (!log) {
      ctx.status = 404
      ctx.body = {
        success: false,
        message: 'SQL日志不存在'
      }
      return
    }

    const updateData = {
      execution_status,
      executed_at: new Date()
    }

    if (execution_time !== undefined) updateData.execution_time = execution_time
    if (rows_affected !== undefined) updateData.rows_affected = rows_affected
    if (error_message) updateData.error_message = error_message
    if (execution_result) updateData.execution_result = execution_result

    await log.update(updateData)

    ctx.body = {
      success: true,
      data: log,
      message: '更新SQL执行结果成功'
    }
  } catch (error) {
    console.error('更新SQL执行结果失败:', error)
    ctx.status = 500
    ctx.body = {
      success: false,
      message: '更新SQL执行结果失败',
      error: error.message
    }
  }
})

// 审批SQL执行
router.put('/:id/approve', auth.checkPermission('system:manage'), async (ctx) => {
  try {
    const { id } = ctx.params
    const { approved } = ctx.request.body
    const approver_id = ctx.state.user.userId

    const log = await AISQLLog.findByPk(id)
    if (!log) {
      ctx.status = 404
      ctx.body = {
        success: false,
        message: 'SQL日志不存在'
      }
      return
    }

    await log.update({
      is_approved: approved,
      approved_by: approved ? approver_id : null,
      approved_at: approved ? new Date() : null
    })

    ctx.body = {
      success: true,
      data: log,
      message: approved ? 'SQL已审批通过' : 'SQL审批已撤销'
    }
  } catch (error) {
    console.error('SQL审批失败:', error)
    ctx.status = 500
    ctx.body = {
      success: false,
      message: 'SQL审批失败',
      error: error.message
    }
  }
})

// 获取SQL日志统计
router.get('/stats/summary', auth.checkPermission('system:manage'), async (ctx) => {
  try {
    const { start_date, end_date } = ctx.query
    
    const where = {}
    if (start_date || end_date) {
      where.created_at = {}
      if (start_date) where.created_at[Op.gte] = new Date(start_date)
      if (end_date) where.created_at[Op.lte] = new Date(end_date)
    }

    // 总体统计
    const totalLogs = await AISQLLog.count({ where })
    
    // 按状态统计
    const statusStats = await AISQLLog.findAll({
      where,
      attributes: [
        'execution_status',
        [sequelize.fn('COUNT', '*'), 'count']
      ],
      group: ['execution_status'],
      raw: true
    })

    // 按SQL类型统计
    const sqlTypeStats = await AISQLLog.findAll({
      where,
      attributes: [
        'sql_type',
        [sequelize.fn('COUNT', '*'), 'count']
      ],
      group: ['sql_type'],
      raw: true
    })

    // 按风险等级统计
    const riskLevelStats = await AISQLLog.findAll({
      where,
      attributes: [
        'risk_level',
        [sequelize.fn('COUNT', '*'), 'count']
      ],
      group: ['risk_level'],
      raw: true
    })

    // 按用户统计
    const userStats = await AISQLLog.findAll({
      where,
      attributes: [
        'user_id',
        [sequelize.fn('COUNT', '*'), 'count']
      ],
      group: ['user_id'],
      include: [{
        model: User,
        as: 'executor',
        attributes: ['username', 'real_name']
      }],
      order: [[sequelize.fn('COUNT', '*'), 'DESC']],
      limit: 10
    })

    ctx.body = {
      success: true,
      data: {
        total: totalLogs,
        statusStats: statusStats.reduce((acc, item) => {
          acc[item.execution_status] = parseInt(item.count)
          return acc
        }, {}),
        sqlTypeStats: sqlTypeStats.reduce((acc, item) => {
          acc[item.sql_type || 'unknown'] = parseInt(item.count)
          return acc
        }, {}),
        riskLevelStats: riskLevelStats.reduce((acc, item) => {
          acc[item.risk_level] = parseInt(item.count)
          return acc
        }, {}),
        topUsers: userStats.map(item => ({
          user_id: item.user_id,
          username: item.executor?.username,
          realName: item.executor?.real_name,
          count: parseInt(item.get('count'))
        }))
      },
      message: '获取SQL日志统计成功'
    }
  } catch (error) {
    console.error('获取SQL日志统计失败:', error)
    ctx.status = 500
    ctx.body = {
      success: false,
      message: '获取SQL日志统计失败',
      error: error.message
    }
  }
})

// 辅助函数：检测SQL类型
function detectSQLType(sqlStatement) {
  const sql = sqlStatement.trim().toUpperCase()
  if (sql.startsWith('SELECT')) return 'SELECT'
  if (sql.startsWith('INSERT')) return 'INSERT'
  if (sql.startsWith('UPDATE')) return 'UPDATE'
  if (sql.startsWith('DELETE')) return 'DELETE'
  if (sql.startsWith('CREATE')) return 'CREATE'
  if (sql.startsWith('ALTER')) return 'ALTER'
  if (sql.startsWith('DROP')) return 'DROP'
  return 'OTHER'
}

// 辅助函数：评估风险等级
function assessRiskLevel(sqlStatement, sqlType) {
  const sql = sqlStatement.toUpperCase()
  
  // 高风险操作
  if (sqlType === 'DROP' || sql.includes('DROP TABLE') || sql.includes('TRUNCATE')) {
    return 'critical'
  }
  
  if (sqlType === 'DELETE' && !sql.includes('WHERE')) {
    return 'critical'
  }
  
  if (sqlType === 'UPDATE' && !sql.includes('WHERE')) {
    return 'high'
  }
  
  // 中风险操作
  if (['CREATE', 'ALTER', 'DELETE', 'UPDATE'].includes(sqlType)) {
    return 'medium'
  }
  
  // 低风险操作
  if (sqlType === 'SELECT') {
    return 'low'
  }
  
  return 'medium'
}

module.exports = router
